package com.example.demo;

public class SQLPrint {

    private static String noahwm_trans_schema="noahwm_trans";

    public static void main(String[] args) {
        String sql  = " SELECT "
                + "        T.ORDER_ID, "
                + "        T.ORDER_TYPE, "
                + "        T.ORDER_STATUS, "
                + "        T.GROUP_NUMBER, "
                + "        T.STRATEGY, "
                + "        T.COUNTERPARTY, "
                + "        T.BUY_SELL_INDICATOR, "
                + "        T.IDENTIFIER_TYPE, "
                + "        T.PRODUCT_ID, "
                + "        T.BATCH_NAME, "
                + "        T.PRODUCT_RATE_ID, "
                + "        T.AMOUNT, "
                + "        T.SHARES, "
                + "        T.SUBSCRIBE_AMOUNT, "
                + "        T.GROSS_NET_PRICE_INDICATOR, "
                + "        T.COMMISSION, "
                + "        T.OTHER_FEES, "
                + "        T.ORDERS, "
                + "        T.DIVIDEND_TYPE, "
                + "        T.ACCOUNT_TYPE, "
                + "        T.ORDER_DATE, "
                + "        T.FILE_DATE, "
                + "        T.LASTMODIFIEDTIME, "
                + "        T.ASSOCIATED_ORDER_ID, "
                + "        T.ASSOCIATED_PARTY, "
                + "        T.TRANSFER_REASON,  "
                + "        T.PAYMENT_TYPE, "
                + "        T.CALL_DUE_DATE, "
                + "        T.IS_ONLINE_TRANSACTION "
                + "   FROM "
                + "        ( "
                + "         SELECT "
                + "                'S_' || SSO.SUBORDERID       AS ORDER_ID, "
                + "                (CASE "
                + "                      WHEN LOWER(SSO.ORDERTYPE) = 'zddlx_buy' THEN '1' "
                + "                 END)                        AS ORDER_TYPE, "
                + "                (CASE "
                + "                      WHEN SSO.ISDELETED = '1'                     THEN '4' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_djy' THEN '1' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_sb'  THEN '4' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_sx'  THEN '4' "
                + "                 END)                        AS ORDER_STATUS, "
                + "                CLIENT.FGROUPNUM             AS GROUP_NUMBER, "
                + "                'NONE'                       AS STRATEGY, "
                + "                'NOAH-HK'                    AS COUNTERPARTY, "
                + "                (CASE "
                + "                      WHEN LOWER(SSO.ORDERTYPE) = 'zddlx_buy' THEN '0' "
                + "                 END)                        AS BUY_SELL_INDICATOR, "
                + "                'NOAH_PROD_ID'               AS IDENTIFIER_TYPE, "
                + "                SO.PRODUCTID                 AS PRODUCT_ID, "
                + "                SO.BATCHCODE                 AS BATCH_NAME, "
                + "                SO.SHARECATID                AS PRODUCT_RATE_ID, "
                + "                TO_NUMBER('0.0000','9.9999') AS AMOUNT, "
                + "                NULL                         AS SHARES, "
                + "                SO.SUBSCRIPTIONAMOUNT        AS SUBSCRIBE_AMOUNT, "
                + "                'G'                          AS GROSS_NET_PRICE_INDICATOR, "
                + "                TO_NUMBER('0.0000','9.9999') AS COMMISSION, "
                + "                SSO.OTHERCOST                AS OTHER_FEES, "
                + "                SSO.TERM                     AS ORDERS, "
                + "                SO.BONUSTYPE                 AS DIVIDEND_TYPE, "
                + "                (CASE WHEN LOWER(SO.ACCOUNTTYPE) = 'direct'  THEN 'D' "
                + "                      WHEN LOWER(SO.ACCOUNTTYPE) = 'nominee' THEN 'N' "
                + "                      WHEN SO.ACCOUNTTYPE IS NULL            THEN 'N' "
                + "                 END)                        AS ACCOUNT_TYPE, "
                + "                SO.SUBSCRIPTIONTIME          AS ORDER_DATE, "
                + "                SYSDATE                      AS FILE_DATE, "
                + "                SSO.LASTMODIFIEDTIME         AS LASTMODIFIEDTIME, "
                + "                ''                           AS ASSOCIATED_ORDER_ID, "
                + "                ''                           AS ASSOCIATED_PARTY, "
                + "                ''                           AS TRANSFER_REASON, "
                + "                (CASE "
                + "                      WHEN  LOWER(SO.PAYMENTTYPE)='pay_type_zz' THEN 'R' "
                + "                      WHEN  LOWER(SO.PAYMENTTYPE)='pay_type_nomineeacct' THEN 'D' "
                + "                      WHEN  LOWER(SO.PAYMENTTYPE)='pay_type_xjb' THEN 'M' "
                + "                 END)                         AS PAYMENT_TYPE, "
                + "                 NULL                         AS CALL_DUE_DATE, "
                + "                 SO.ISONLINETRANSACTION       AS IS_ONLINE_TRANSACTION "
                + "           FROM "
                + "                ( "
                + "                 SELECT "
                + "                        SUBORDER.* "
                + "                   FROM "
                + "                        TBL_SALE_SUB_ORDER SUBORDER "
                + "                  INNER JOIN "
                + "                        ( "
                + "                         SELECT "
                + "                                ORDERID, "
                + "                                MIN(TO_NUMBER(NVL(TERM,'0.0'),'99.99')) AS MIN_ORDERS "
                + "                           FROM "
                + "                                TBL_SALE_SUB_ORDER "
                + "                          WHERE "
                + "                                ( "
                + "                                    LOWER(SUBORDERSTATUS) = 'zddzt_djy' "
                + "                                 OR LOWER(SUBORDERSTATUS) = 'zddzt_sb' "
                + "                                 OR LOWER(SUBORDERSTATUS) = 'zddzt_sx') "
                + "                            AND LASTMODIFIEDBYID <> 'LinedataUser' "
                + "                          GROUP BY "
                + "                                ORDERID "
                + "                        ) MINORDER "
                + "                     ON SUBORDER.ORDERID = MINORDER.ORDERID "
                + "                    AND TO_NUMBER(NVL(SUBORDER.TERM,'0.0'),'99.99') = MINORDER.MIN_ORDERS "
                + "                    AND SUBORDER.ISDELETED = '0' "
                + "                ) SSO "
                + "          INNER JOIN "
                + "                TBL_SALE_ORDER SO "
                + "             ON SO.ORDERID = SSO.ORDERID "
                + "            AND SO.ISDELETED = '0' "
                + "          INNER JOIN "
                + "                TBL_SYS_CLIENT CLIENT "
                + "             ON CLIENT.CLIENTID = SSO.CLIENTID "
                + "            AND CLIENT.ISDELETED = '0' "
                + "          INNER JOIN "
                + "                TBL_DVL_PRODUCT PRODUCT "
                + "             ON PRODUCT.PRODUCTID = SO.PRODUCTID "
                + "            AND PRODUCT.ISDELETED = '0' "
                + "            AND PRODUCT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_DVL_PROJECT PROJECT "
                + "             ON PROJECT.PROJECTID = PRODUCT.PROJECTID "
                + "            AND PROJECT.ISDELETED = '0' "
                + "            AND PROJECT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_DVL_CONTRACT CONTRACT "
                + "             ON CONTRACT.CONTRACTID = PRODUCT.CONTRACTID "
                + "            AND LOWER(CONTRACT.TRADINGPLATFORM) = 'nyjtfl_3' "
                + "            AND CONTRACT.ISDELETED = '0' "
                + "            AND CONTRACT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_TSK_SIGNING SIGNING "
                + "             ON SIGNING.ORDERID = SSO.ORDERID "
                + "            AND LOWER(SIGNING.SIGNINGSTATUS) = 'contractstatus_pass' "
                + "            AND SIGNING.LASTMODIFIEDTIME = (SELECT "
                + "                                                   MAX(LASTMODIFIEDTIME) "
                + "                                              FROM "
                + "                                                   TBL_TSK_SIGNING T "
                + "                                             WHERE "
                + "                                                   T.ORDERID = SSO.ORDERID "
                + "                                               AND T.ISDELETED = '0') "
                + "            AND SIGNING.ISDELETED = '0' "
                + "          WHERE "
                + "                LOWER(SSO.ORDERTYPE) = 'zddlx_buy' "
                + "            AND (  UPPER(SO.ORDERINTERNALSOURCE) <> 'YWJHLYQDHKAPP' "
                + "                 OR CONTRACT.PROJECTID <> '405937938dea46c49784e1b9381821cb') "
                + "  "
                + "         UNION ALL "
                + "  "
                + "         SELECT "
                + "                'S_' || SSO.SUBORDERID       AS ORDER_ID, "
                + "                (CASE "
                + "                      WHEN LOWER(SSO.ORDERTYPE) = 'zddlx_buy' THEN '1' "
                + "                 END)                        AS ORDER_TYPE, "
                + "                (CASE "
                + "                      WHEN SSO.ISDELETED = '1'                     THEN '4' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_djy' THEN '1' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_sb'  THEN '4' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_sx'  THEN '4' "
                + "                 END)                        AS ORDER_STATUS, "
                + "                CLIENT.FGROUPNUM             AS GROUP_NUMBER, "
                + "                'NONE'                       AS STRATEGY, "
                + "                'BIFAST'                     AS COUNTERPARTY, "
                + "                (CASE "
                + "                      WHEN LOWER(SSO.ORDERTYPE) = 'zddlx_buy' THEN '0' "
                + "                 END)                        AS BUY_SELL_INDICATOR, "
                + "                'NOAH_PROD_ID'               AS IDENTIFIER_TYPE, "
                + "                SO.PRODUCTID                 AS PRODUCT_ID, "
                + "                SO.BATCHCODE                 AS BATCH_NAME, "
                + "                SO.SHARECATID                AS PRODUCT_RATE_ID, "
                + "                TO_NUMBER('0.0000','9.9999') AS AMOUNT, "
                + "                NULL                         AS SHARES, "
                + "                SO.SUBSCRIPTIONAMOUNT        AS SUBSCRIBE_AMOUNT, "
                + "                'G'                          AS GROSS_NET_PRICE_INDICATOR, "
                + "                TO_NUMBER('0.0000','9.9999') AS COMMISSION, "
                + "                SSO.OTHERCOST                AS OTHER_FEES, "
                + "                SSO.TERM                     AS ORDERS, "
                + "                SO.BONUSTYPE                 AS DIVIDEND_TYPE, "
                + "                (CASE WHEN LOWER(SO.ACCOUNTTYPE) = 'direct'  THEN 'D' "
                + "                      WHEN LOWER(SO.ACCOUNTTYPE) = 'nominee' THEN 'N' "
                + "                      WHEN SO.ACCOUNTTYPE IS NULL            THEN 'N' "
                + "                 END)                        AS ACCOUNT_TYPE, "
                + "                SO.SUBSCRIPTIONTIME          AS ORDER_DATE, "
                + "                SYSDATE                      AS FILE_DATE, "
                + "                SSO.LASTMODIFIEDTIME         AS LASTMODIFIEDTIME, "
                + "                ''                           AS ASSOCIATED_ORDER_ID, "
                + "                ''                           AS ASSOCIATED_PARTY, "
                + "                ''                           AS TRANSFER_REASON, "
                + "                (CASE "
                + "                      WHEN  LOWER(SO.PAYMENTTYPE)='pay_type_zz' THEN 'R' "
                + "                      WHEN  LOWER(SO.PAYMENTTYPE)='pay_type_nomineeacct' THEN 'D' "
                + "                      WHEN  LOWER(SO.PAYMENTTYPE)='pay_type_xjb' THEN 'M' "
                + "                 END)                        AS PAYMENT_TYPE, "
                + "                 NULL                         AS CALL_DUE_DATE, "
                + "                 SO.ISONLINETRANSACTION       AS IS_ONLINE_TRANSACTION "
                + "           FROM "
                + "                ( "
                + "                 SELECT "
                + "                        SUBORDER.* "
                + "                   FROM "
                + "                        TBL_SALE_SUB_ORDER SUBORDER "
                + "                  INNER JOIN "
                + "                        ( "
                + "                         SELECT "
                + "                                ORDERID, "
                + "                                MIN(TO_NUMBER(NVL(TERM,'0.0'),'99.99')) AS MIN_ORDERS "
                + "                           FROM "
                + "                                TBL_SALE_SUB_ORDER "
                + "                          WHERE "
                + "                                ( "
                + "                                    LOWER(SUBORDERSTATUS) = 'zddzt_djy' "
                + "                                 OR LOWER(SUBORDERSTATUS) = 'zddzt_sb' "
                + "                                 OR LOWER(SUBORDERSTATUS) = 'zddzt_sx') "
                + "                            AND LASTMODIFIEDBYID <> 'LinedataUser' "
                + "                          GROUP BY "
                + "                                ORDERID "
                + "                        ) MINORDER "
                + "                     ON SUBORDER.ORDERID = MINORDER.ORDERID "
                + "                    AND TO_NUMBER(NVL(SUBORDER.TERM,'0.0'),'99.99') = MINORDER.MIN_ORDERS "
                + "                    AND SUBORDER.ISDELETED = '0' "
                + "                ) SSO "
                + "          INNER JOIN "
                + "                TBL_SALE_ORDER SO "
                + "             ON SO.ORDERID = SSO.ORDERID "
                + "            AND SO.ISDELETED = '0' "
                + "          INNER JOIN "
                + "                TBL_SYS_CLIENT CLIENT "
                + "             ON CLIENT.CLIENTID = SSO.CLIENTID "
                + "            AND CLIENT.ISDELETED = '0' "
                + "          INNER JOIN "
                + "                TBL_DVL_PRODUCT PRODUCT "
                + "             ON PRODUCT.PRODUCTID = SO.PRODUCTID "
                + "            AND PRODUCT.ISDELETED = '0' "
                + "            AND PRODUCT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_DVL_PROJECT PROJECT "
                + "             ON PROJECT.PROJECTID = PRODUCT.PROJECTID "
                + "            AND PROJECT.ISDELETED = '0' "
                + "            AND PROJECT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_DVL_CONTRACT CONTRACT "
                + "             ON CONTRACT.CONTRACTID = PRODUCT.CONTRACTID "
                + "            AND LOWER(CONTRACT.TRADINGPLATFORM) = 'nyjtfl_3' "
                + "            AND CONTRACT.ISDELETED = '0' "
                + "            AND CONTRACT.ISVALID = '1' "
                + "          WHERE "
                + "                LOWER(SSO.ORDERTYPE) = 'zddlx_buy' "
                + "            AND UPPER(SO.ORDERINTERNALSOURCE) = 'YWJHLYQDHKAPP' "
                + "            AND CONTRACT.PROJECTID = '405937938dea46c49784e1b9381821cb' "
                + "  "
                + "         UNION ALL "
                + "  "
                + "         SELECT "
                + "                'C_' || CST.CLIENTSUBTASKID  AS ORDER_ID, "
                + "                (CASE "
                + "                      WHEN LOWER(CST.TASKTYPE) = 'tasktype_call' THEN '2' "
                + "                 END)                        AS ORDER_TYPE, "
                + "                (CASE "
                + "                      WHEN CST.ISDELETED = '1'      THEN '4' "
                + "                      WHEN LOWER(CST.CLIENTSUBTASKSTATUS) = 'indivtaskstatus_to_process' THEN '1' "
                + "                      WHEN LOWER(CST.CLIENTSUBTASKSTATUS) = 'indivtaskstatus_invalid'    THEN '4' "
                + "                 END)                        AS ORDER_STATUS, "
                + "                CLIENT.FGROUPNUM             AS GROUP_NUMBER, "
                + "                'NONE'                       AS STRATEGY, "
                + "                'NOAH-HK'                    AS COUNTERPARTY, "
                + "                (CASE "
                + "                      WHEN LOWER(CST.TASKTYPE) = 'tasktype_call' THEN '0' "
                + "                 END)                        AS BUY_SELL_INDICATOR, "
                + "                'NOAH_PROD_ID'               AS IDENTIFIER_TYPE, "
                + "                CST.PRODUCTID                AS PRODUCT_ID, "
                + "                CST.BATCHCODE                AS BATCH_NAME, "
                + "                NVL(DI.SHARECATID,PR.SHARECATID)                AS PRODUCT_RATE_ID, "
                + "                CST.CALLMONEY                AS AMOUNT, "
                + "                NULL                         AS SHARES, "
                + "                CST.PURCHASEAMOUNT           AS SUBSCRIBE_AMOUNT, "
                + "                'G'                          AS GROSS_NET_PRICE_INDICATOR, "
                + "                TO_NUMBER('0.0000','9.9999') AS COMMISSION, "
                + "                SSO.OTHERCOST                AS OTHER_FEES, "
                + "                ST.CALLTERM                  AS ORDERS, "
                + "                NVL(DI.DIVIDENDTYPE, 'DT_C')    AS DIVIDEND_TYPE, "
                + "                (CASE WHEN LOWER(DI.ACCOUNTTYPE) = 'direct'  THEN 'D' "
                + "                      WHEN LOWER(DI.ACCOUNTTYPE) = 'nominee' THEN 'N' "
                + "                      WHEN DI.ACCOUNTTYPE IS NULL            THEN 'N' "
                + "                 END)                        AS ACCOUNT_TYPE, "
                + "                CST.CREATEDTIME              AS ORDER_DATE, "
                + "                SYSDATE                      AS FILE_DATE, "
                + "                CST.LASTMODIFIEDTIME         AS LASTMODIFIEDTIME, "
                + "                ''                           AS ASSOCIATED_ORDER_ID, "
                + "                ''                           AS ASSOCIATED_PARTY, "
                + "                ''                           AS TRANSFER_REASON, "
                + "                ''                           AS PAYMENT_TYPE, "
                + "                ST.TASKENDDATE              AS CALL_DUE_DATE, "
                + "                SO.ISONLINETRANSACTION       AS IS_ONLINE_TRANSACTION "
                + "           FROM "
                + "                ( "
                + "                 SELECT "
                + "                        * "
                + "                   FROM "
                + "                        TBL_PMD_CLIENT_SUBTASK "
                + "                  WHERE "
                + "                        (   LOWER(CLIENTSUBTASKSTATUS) = 'indivtaskstatus_to_process' "
                + "                         OR LOWER(CLIENTSUBTASKSTATUS) = 'indivtaskstatus_invalid') "
                + "                    AND LASTMODIFIEDBYID <> 'LinedataUser' "
                + "                ) CST "
                + "          INNER JOIN "
                + "                TBL_SYS_CLIENT CLIENT "
                + "             ON CLIENT.CLIENTID = CST.CLIENTID "
                + "            AND CLIENT.ISDELETED = '0' "
                + "          INNER JOIN "
                + "                TBL_PMD_SURVIVAL_TASK ST "
                + "             ON ST.SURVIVALTASKID = CST.SURVIVALTASKID "
                + "            AND ST.TASKSTATUS IN ('taskstatus_review_pass','taskstatus_approve_pass','taskstatus_inprogress','taskstatus_invalid','taskstatus_finished') "
                + "           LEFT JOIN "
                +                  noahwm_trans_schema + ".TBL_DEAL_INVESTMENT DI "
                + "             ON DI.DEALINVESTMENTID = CST.DEALINVESTMENTID "
                + "            AND DI.ISDELETED = '0' "
                + "           LEFT JOIN "
                + "                TBL_SALE_SUB_ORDER SSO "
                + "             ON SSO.SUBORDERID = DI.DEALSOURCEOBJID "
                + "            AND SSO.ISDELETED = '0' "
                + "           LEFT JOIN "
                + "                TBL_SALE_ORDER SO "
                + "             ON (   SO.ORDERID = SSO.ORDERID "
                + "                 OR SO.ORDERID = DI.DEALSOURCEOBJID) "
                + "            AND SO.ISDELETED = '0' "
                + "          INNER JOIN "
                + "                TBL_DVL_PRODUCT PRODUCT "
                + "             ON PRODUCT.PRODUCTID = CST.PRODUCTID "
                + "            AND PRODUCT.ISDELETED = '0' "
                + "            AND PRODUCT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_DVL_PROJECT PROJECT "
                + "             ON PROJECT.PROJECTID = PRODUCT.PROJECTID "
                + "            AND PROJECT.ISDELETED = '0' "
                + "            AND PROJECT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_DVL_CONTRACT CONTRACT "
                + "             ON CONTRACT.CONTRACTID = PRODUCT.CONTRACTID "
                + "            AND LOWER(CONTRACT.TRADINGPLATFORM) = 'nyjtfl_3' "
                + "            AND CONTRACT.ISDELETED = '0' "
                + "            AND CONTRACT.ISVALID = '1' "
                + "           LEFT JOIN ( "
                + "                       SELECT "
                + "                             T.SHARECATID, "
                + "                             T.PRODUCTID, "
                + "                             (CASE WHEN LOWER(T.ISINCLUDELOWERLIMIT) = 'dy' "
                + "                                   THEN T.LOWERLIMITAMOUNT + 1 "
                + "                                   ELSE T.LOWERLIMITAMOUNT "
                + "                              END) AS LEVELLOWER, "
                + "                             (CASE WHEN LOWER(T.ISINCLUDEUPPERLIMIT) = 'xy' "
                + "                                   THEN T.UPPERLIMITAMOUNT - 1 "
                + "                                   ELSE T.UPPERLIMITAMOUNT "
                + "                              END) AS LEVELUPPER "
                + "                        FROM TBL_DVL_PRODUCT_SHARE_CAT T "
                + "                       WHERE T.ISDELETED = 0 "
                + "                     ) PR "
                + "             ON PR.PRODUCTID   = PRODUCT.PRODUCTID "
                + "            AND (   PR.LEVELLOWER <= CST.PURCHASEAMOUNT "
                + "                 OR PR.LEVELLOWER = 0) "
                + "            AND (   PR.LEVELUPPER >= CST.PURCHASEAMOUNT "
                + "                 OR PR.LEVELUPPER IS NULL) "
                + "          WHERE "
                + "                LOWER(CST.TASKTYPE) = 'tasktype_call' "
                + "  "
                + "         UNION ALL "
                + "  "
                + "         SELECT "
                + "                'R_' || SSO.SUBORDERID       AS ORDER_ID, "
                + "                (CASE "
                + "                      WHEN LOWER(SSO.ORDERTYPE) = 'zddlx_sale' THEN '3' "
                + "                 END)                        AS ORDER_TYPE, "
                + "                (CASE "
                + "                      WHEN SSO.ISDELETED = '1'                     THEN '4' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_djy' THEN '1' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_sb'  THEN '4' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_sx'  THEN '4' "
                + "                 END)                        AS ORDER_STATUS, "
                + "                CLIENT.FGROUPNUM             AS GROUP_NUMBER, "
                + "                'NONE'                       AS STRATEGY, "
                + "                'NOAH-HK'                    AS COUNTERPARTY, "
                + "                (CASE "
                + "                      WHEN LOWER(SSO.ORDERTYPE) = 'zddlx_sale' THEN '1' "
                + "                 END)                        AS BUY_SELL_INDICATOR, "
                + "                'NOAH_PROD_ID'               AS IDENTIFIER_TYPE, "
                + "                RA.PRODUCTID                 AS PRODUCT_ID, "
                + "                RA.BATCHCODE                 AS BATCH_NAME, "
                + "                NVL(RA.SHARECATID,PR.SHARECATID)      AS PRODUCT_RATE_ID, "
                + "                NULL                         AS AMOUNT, "
                + "                SSO.SUBORDERRDMSHARE         AS SHARES, "
                + "                NULL                         AS SUBSCRIBE_AMOUNT, "
                + "                'G'                          AS GROSS_NET_PRICE_INDICATOR, "
                + "                TO_NUMBER('0.0000','9.9999') AS COMMISSION, "
                + "                NULL                         AS OTHER_FEES, "
                + "                NULL                         AS ORDERS, "
                + "                NVL(RA.BONUSTYPE,'DT_C')     AS DIVIDEND_TYPE, "
                + "                (CASE WHEN LOWER(RA.ACCOUNTTYPE) = 'direct'  THEN 'D'  "
                + "                WHEN LOWER(RA.ACCOUNTTYPE) = 'nominee' THEN 'N' "
                + "                WHEN RA.ACCOUNTTYPE IS NULL            THEN 'N' "
                + "                END)                         AS ACCOUNT_TYPE,"
                + "                RA.RDMAPPLYTIME              AS ORDER_DATE, "
                + "                SYSDATE                      AS FILE_DATE, "
                + "                SSO.LASTMODIFIEDTIME         AS LASTMODIFIEDTIME, "
                + "                ''                           AS ASSOCIATED_ORDER_ID, "
                + "                ''                           AS ASSOCIATED_PARTY, "
                + "                ''                           AS TRANSFER_REASON, "
                + "                ''                           AS PAYMENT_TYPE, "
                + "                NULL                         AS CALL_DUE_DATE, "
                + "                RA.ISONLINETRANSACTION       AS IS_ONLINE_TRANSACTION "
                + "           FROM "
                + "                ( "
                + "                 SELECT "
                + "                        * "
                + "                   FROM "
                + "                        TBL_SALE_SUB_ORDER "
                + "                  WHERE "
                + "                        (   LOWER(SUBORDERSTATUS) = 'zddzt_djy' "
                + "                         OR LOWER(SUBORDERSTATUS) = 'zddzt_sb' "
                + "                         OR LOWER(SUBORDERSTATUS) = 'zddzt_sx') "
                + "                    AND LASTMODIFIEDBYID <> 'LinedataUser' "
                + "                ) SSO "
                + "          INNER JOIN "
                + "                TBL_SALE_RDM_APPLY RA "
                + "             ON RA.RDMAPPLYID = SSO.ORDERID "
                + "            AND RA.ISDELETED = '0' "
                + "          INNER JOIN "
                + "                TBL_SYS_CLIENT CLIENT "
                + "             ON CLIENT.CLIENTID = SSO.CLIENTID "
                + "            AND CLIENT.ISDELETED = '0' "
                + "          INNER JOIN "
                + "                TBL_DVL_PRODUCT PRODUCT "
                + "             ON PRODUCT.PRODUCTID = RA.PRODUCTID "
                + "            AND PRODUCT.ISDELETED = '0' "
                + "            AND PRODUCT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_DVL_PROJECT PROJECT "
                + "             ON PROJECT.PROJECTID = PRODUCT.PROJECTID "
                + "            AND PROJECT.ISDELETED = '0' "
                + "            AND PROJECT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_DVL_CONTRACT CONTRACT "
                + "             ON CONTRACT.CONTRACTID = PRODUCT.CONTRACTID "
                + "            AND LOWER(CONTRACT.TRADINGPLATFORM) = 'nyjtfl_3' "
                + "            AND CONTRACT.ISDELETED = '0' "
                + "            AND CONTRACT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_TSK_SIGNING SIGNING "
                + "             ON SIGNING.ORDERID = SSO.ORDERID "
                + "            AND LOWER(SIGNING.SIGNINGSTATUS) = 'contractstatus_pass' "
                + "            AND SIGNING.LASTMODIFIEDTIME = (SELECT "
                + "                                                   MAX(LASTMODIFIEDTIME) "
                + "                                              FROM "
                + "                                                   TBL_TSK_SIGNING T "
                + "                                             WHERE "
                + "                                                   T.ORDERID = SSO.ORDERID "
                + "                                               AND T.ISDELETED = '0') "
                + "            AND SIGNING.ISDELETED = '0' "
                + "          INNER JOIN "
                +                  noahwm_trans_schema + ".TBL_STATICSHARE_BATCH VSR "
                + "             ON VSR.PRODUCTID = RA.PRODUCTID "
                + "            AND VSR.FGROUPNUM = CLIENT.FGROUPNUM "
                + "            AND VSR.BATCHCODE = RA.BATCHCODE "
                + "           LEFT JOIN ( "
                + "                       SELECT "
                + "                             T.SHARECATID, "
                + "                             T.PRODUCTID, "
                + "                             (CASE WHEN LOWER(T.ISINCLUDELOWERLIMIT) = 'dy' "
                + "                                   THEN T.LOWERLIMITAMOUNT + 1 "
                + "                                   ELSE T.LOWERLIMITAMOUNT "
                + "                              END) AS LEVELLOWER, "
                + "                             (CASE WHEN LOWER(T.ISINCLUDEUPPERLIMIT) = 'xy' "
                + "                                   THEN T.UPPERLIMITAMOUNT - 1 "
                + "                                   ELSE T.UPPERLIMITAMOUNT "
                + "                              END) AS LEVELUPPER "
                + "                        FROM TBL_DVL_PRODUCT_SHARE_CAT T "
                + "                       WHERE T.ISDELETED = 0 "
                + "                     ) PR "
                + "             ON PR.PRODUCTID   = PRODUCT.PRODUCTID "
                + "            AND (   PR.LEVELLOWER <= VSR.SUBSCRIPTIONAMOUNT "
                + "                 OR PR.LEVELLOWER = 0) "
                + "            AND (   PR.LEVELUPPER >= VSR.SUBSCRIPTIONAMOUNT "
                + "                 OR PR.LEVELUPPER IS NULL) "
                + "          WHERE "
                + "                LOWER(SSO.ORDERTYPE) = 'zddlx_sale' "
                + "            AND (   UPPER(RA.ORDERINTERNALSOURCE) <> 'YWJHLYQDHKAPP' "
                + "                 OR CONTRACT.PROJECTID <> '405937938dea46c49784e1b9381821cb') "
                + "  "
                + "         UNION ALL "
                + "  "
                + "         SELECT "
                + "                'R_' || SSO.SUBORDERID       AS ORDER_ID, "
                + "                (CASE "
                + "                      WHEN LOWER(SSO.ORDERTYPE) = 'zddlx_sale' THEN '3' "
                + "                 END)                        AS ORDER_TYPE, "
                + "                (CASE "
                + "                      WHEN SSO.ISDELETED = '1'                     THEN '4' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_djy' THEN '1' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_sb'  THEN '4' "
                + "                      WHEN LOWER(SSO.SUBORDERSTATUS) = 'zddzt_sx'  THEN '4' "
                + "                 END)                        AS ORDER_STATUS, "
                + "                CLIENT.FGROUPNUM             AS GROUP_NUMBER, "
                + "                'NONE'                       AS STRATEGY, "
                + "                'BIFAST'                     AS COUNTERPARTY, "
                + "                (CASE "
                + "                      WHEN LOWER(SSO.ORDERTYPE) = 'zddlx_sale' THEN '1' "
                + "                 END)                        AS BUY_SELL_INDICATOR, "
                + "                'NOAH_PROD_ID'               AS IDENTIFIER_TYPE, "
                + "                RA.PRODUCTID                 AS PRODUCT_ID, "
                + "                RA.BATCHCODE                 AS BATCH_NAME, "
                + "                NVL(RA.SHARECATID,PR.SHARECATID)      AS PRODUCT_RATE_ID, "
                + "                NULL                         AS AMOUNT, "
                + "                SSO.SUBORDERRDMSHARE         AS SHARES, "
                + "                NULL                         AS SUBSCRIBE_AMOUNT, "
                + "                'G'                          AS GROSS_NET_PRICE_INDICATOR, "
                + "                TO_NUMBER('0.0000','9.9999') AS COMMISSION, "
                + "                NULL                         AS OTHER_FEES, "
                + "                NULL                         AS ORDERS, "
                + "                NVL(RA.BONUSTYPE,'DT_C')     AS DIVIDEND_TYPE, "
                + "                (CASE WHEN LOWER(RA.ACCOUNTTYPE) = 'direct'  THEN 'D'  "
                + "                WHEN LOWER(RA.ACCOUNTTYPE) = 'nominee' THEN 'N' "
                + "                WHEN RA.ACCOUNTTYPE IS NULL            THEN 'N' "
                + "                END)                         AS ACCOUNT_TYPE,"
                + "                RA.RDMAPPLYTIME              AS ORDER_DATE, "
                + "                SYSDATE                      AS FILE_DATE, "
                + "                SSO.LASTMODIFIEDTIME         AS LASTMODIFIEDTIME, "
                + "                ''                           AS ASSOCIATED_ORDER_ID, "
                + "                ''                           AS ASSOCIATED_PARTY, "
                + "                ''                           AS TRANSFER_REASON, "
                + "                ''                           AS PAYMENT_TYPE, "
                + "                NULL                         AS CALL_DUE_DATE, "
                + "                RA.ISONLINETRANSACTION       AS IS_ONLINE_TRANSACTION "
                + "           FROM "
                + "                ( "
                + "                 SELECT "
                + "                        * "
                + "                   FROM "
                + "                        TBL_SALE_SUB_ORDER "
                + "                  WHERE "
                + "                        (   LOWER(SUBORDERSTATUS) = 'zddzt_djy' "
                + "                         OR LOWER(SUBORDERSTATUS) = 'zddzt_sb' "
                + "                         OR LOWER(SUBORDERSTATUS) = 'zddzt_sx') "
                + "                    AND LASTMODIFIEDBYID <> 'LinedataUser' "
                + "                ) SSO "
                + "          INNER JOIN "
                + "                TBL_SALE_RDM_APPLY RA "
                + "             ON RA.RDMAPPLYID = SSO.ORDERID "
                + "            AND RA.ISDELETED = '0' "
                + "          INNER JOIN "
                + "                TBL_SYS_CLIENT CLIENT "
                + "             ON CLIENT.CLIENTID = SSO.CLIENTID "
                + "            AND CLIENT.ISDELETED = '0' "
                + "          INNER JOIN "
                + "                TBL_DVL_PRODUCT PRODUCT "
                + "             ON PRODUCT.PRODUCTID = RA.PRODUCTID "
                + "            AND PRODUCT.ISDELETED = '0' "
                + "            AND PRODUCT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_DVL_PROJECT PROJECT "
                + "             ON PROJECT.PROJECTID = PRODUCT.PROJECTID "
                + "            AND PROJECT.ISDELETED = '0' "
                + "            AND PROJECT.ISVALID = '1' "
                + "          INNER JOIN "
                + "                TBL_DVL_CONTRACT CONTRACT "
                + "             ON CONTRACT.CONTRACTID = PRODUCT.CONTRACTID "
                + "            AND LOWER(CONTRACT.TRADINGPLATFORM) = 'nyjtfl_3' "
                + "            AND CONTRACT.ISDELETED = '0' "
                + "            AND CONTRACT.ISVALID = '1' "
                + "          INNER JOIN "
                +                  noahwm_trans_schema + ".TBL_STATICSHARE_BATCH VSR "
                + "             ON VSR.PRODUCTID = RA.PRODUCTID "
                + "            AND VSR.FGROUPNUM = CLIENT.FGROUPNUM "
                + "            AND VSR.BATCHCODE = RA.BATCHCODE "
                + "           LEFT JOIN ( "
                + "                       SELECT "
                + "                             T.SHARECATID, "
                + "                             T.PRODUCTID, "
                + "                             (CASE WHEN LOWER(T.ISINCLUDELOWERLIMIT) = 'dy' "
                + "                                   THEN T.LOWERLIMITAMOUNT + 1 "
                + "                                   ELSE T.LOWERLIMITAMOUNT "
                + "                              END) AS LEVELLOWER, "
                + "                             (CASE WHEN LOWER(T.ISINCLUDEUPPERLIMIT) = 'xy' "
                + "                                   THEN T.UPPERLIMITAMOUNT - 1 "
                + "                                   ELSE T.UPPERLIMITAMOUNT "
                + "                              END) AS LEVELUPPER "
                + "                        FROM TBL_DVL_PRODUCT_SHARE_CAT T "
                + "                       WHERE T.ISDELETED = 0 "
                + "                     ) PR "
                + "             ON PR.PRODUCTID   = PRODUCT.PRODUCTID "
                + "            AND (   PR.LEVELLOWER <= VSR.SUBSCRIPTIONAMOUNT "
                + "                 OR PR.LEVELLOWER = 0) "
                + "            AND (   PR.LEVELUPPER >= VSR.SUBSCRIPTIONAMOUNT "
                + "                 OR PR.LEVELUPPER IS NULL) "
                + "          WHERE "
                + "                LOWER(SSO.ORDERTYPE) = 'zddlx_sale' "
                + "            AND UPPER(RA.ORDERINTERNALSOURCE) = 'YWJHLYQDHKAPP' "
                + "            AND CONTRACT.PROJECTID = '405937938dea46c49784e1b9381821cb' "
                + "  "
                + "         UNION ALL "
                + "  "
                + "         SELECT "
                + "         'O_' || TR.FUNDTRANSFERRECID AS ORDER_ID, "
                + "         '22' AS ORDER_TYPE, "
                + "         (CASE "
                + "         WHEN LOWER(TR.STATUS) = 'transferstatus_invalid' OR "
                + "         TR.ISDELETED = '1' THEN   '4' "
                + "         WHEN LOWER(TR.STATUS) = 'transferstatus_confirmed' THEN  '1' "
                + "         END) AS ORDER_STATUS, "
                + "         C.FGROUPNUM AS GROUP_NUMBER, "
                + "         'NONE' AS STRATEGY, "
                + "         'NOAH-HK' AS COUNTERPARTY, "
                + "         '1' AS BUY_SELL_INDICATOR, "
                + "         'NOAH_PROD_ID' AS IDENTIFIER_TYPE, "
                + "         TR.PRODUCTID AS PRODUCT_ID, "
                + "         TR.BATCHCODE AS BATCH_NAME, "
                + "         TR.SHARECATID AS PRODUCT_RATE_ID, "
                + "         NULL AS AMOUNT, "
                + "         TR.PROPERTYSHARE AS SHARES, "
                + "         TR.SUBSCRIPTIONAMOUNT AS SUBSCRIBE_AMOUNT, "
                + "         'G' AS GROSS_NET_PRICE_INDICATOR, "
                + "         NULL AS COMMISSION, "
                + "         NULL AS OTHER_FEES, "
                + "         '' AS ORDERS, "
                + "         TR.DIVIDENDTYPE AS DIVIDEND_TYPE, "
                + "         (CASE "
                + "         WHEN LOWER(TR.ACCOUNTTYPE) = 'nominee' THEN  'N' "
                + "         WHEN LOWER(TR.ACCOUNTTYPE) = 'direct'  THEN  'D'"
                + "         ELSE NULL "
                + "         END) AS ACCOUNT_TYPE, "
                + "         TR.RECDATE AS ORDER_DATE, "
                + "         SYSDATE AS FILE_DATE, "
                + "         TR.LASTMODIFIEDTIME AS LASTMODIFIEDTIME, "
                + "         'I_' || TR.FUNDTRANSFERRECID AS ASSOCIATED_ORDER_ID, "
                + "         TR.ISCONNECTEDCLIENT AS ASSOCIATED_PARTY, "
                + "         TR.TRANSFERREASON AS TRANSFER_REASON, "
                + "         ''                AS PAYMENT_TYPE, "
                + "         NULL              AS CALL_DUE_DATE, "
                + "         NULL       		  AS IS_ONLINE_TRANSACTION "
                + "         FROM TBL_TSK_FUND_TRANSFER_REC TR "
                + "         INNER JOIN TBL_SYS_CLIENT C "
                + "            ON TR.TRANSFERCLIENTID = C.CLIENTID "
                + "            AND C.ISDELETED = '0' "
                + "         INNER JOIN TBL_DVL_PRODUCT PRODUCT "
                + "            ON PRODUCT.PRODUCTID = TR.PRODUCTID "
                + "            AND PRODUCT.ISDELETED = '0' "
                + "            AND PRODUCT.ISVALID = '1' "
                + "         INNER JOIN TBL_DVL_CONTRACT CONTRACT "
                + "            ON CONTRACT.CONTRACTID = PRODUCT.CONTRACTID "
                + "            AND CONTRACT.TRADINGPLATFORM = 'nyjtfl_3' "
                + "            AND CONTRACT.ISDELETED = '0' "
                + "            AND CONTRACT.ISVALID = '1' "
                + "         WHERE (TR.STATUS = 'transferstatus_confirmed' "
                + "            OR TR.STATUS = 'transferstatus_invalid') "
                + "            AND CONTRACT.PROJECTID <> '405937938dea46c49784e1b9381821cb' "
                + "  "
                + "         UNION ALL "
                + "  "
                + "         SELECT "
                + "         'I_' || TR.FUNDTRANSFERRECID AS ORDER_ID, "
                + "         '23' AS ORDER_TYPE, "
                + "         (CASE "
                + "         WHEN LOWER(TR.STATUS) = 'transferstatus_invalid' OR "
                + "         TR.ISDELETED = '1' THEN   '4' "
                + "         WHEN LOWER(TR.STATUS) = 'transferstatus_confirmed' THEN  '1' "
                + "         END) AS ORDER_STATUS, "
                + "         C.FGROUPNUM AS GROUP_NUMBER, "
                + "         'NONE' AS STRATEGY, "
                + "         'NOAH-HK' AS COUNTERPARTY, "
                + "         '0' AS BUY_SELL_INDICATOR, "
                + "         'NOAH_PROD_ID' AS IDENTIFIER_TYPE, "
                + "         TR.PRODUCTID AS PRODUCT_ID, "
                + "         TR.BATCHCODE AS BATCH_NAME, "
                + "         TR.SHARECATID AS PRODUCT_RATE_ID, "
                + "         TR.PRINCIPALAMOUNT AS AMOUNT, "
                + "         NULL AS SHARES, "
                + "         TR.SUBSCRIPTIONAMOUNT AS SUBSCRIBE_AMOUNT, "
                + "         'G' AS GROSS_NET_PRICE_INDICATOR, "
                + "         NULL AS COMMISSION, "
                + "         NULL AS OTHER_FEES, "
                + "         '' AS ORDERS, "
                + "         TR.DIVIDENDTYPE AS DIVIDEND_TYPE, "
                + "         (CASE "
                + "         WHEN LOWER(TR.ACCEPTACCOUNTTYPE) = 'nominee' THEN  'N' "
                + "         WHEN LOWER(TR.ACCEPTACCOUNTTYPE) = 'direct'  THEN  'D'"
                + "         ELSE NULL "
                + "         END) AS ACCOUNT_TYPE, "
                + "         TR.RECDATE AS ORDER_DATE, "
                + "         SYSDATE AS FILE_DATE, "
                + "         TR.LASTMODIFIEDTIME AS LASTMODIFIEDTIME, "
                + "         'O_' || TR.FUNDTRANSFERRECID AS ASSOCIATED_ORDER_ID, "
                + "         TR.ISCONNECTEDCLIENT AS ASSOCIATED_PARTY, "
                + "         TR.TRANSFERREASON AS TRANSFER_REASON, "
                + "         ''                AS PAYMENT_TYPE, "
                + "         NULL              AS CALL_DUE_DATE, "
                + "         NULL       		  AS IS_ONLINE_TRANSACTION "
                + "         FROM TBL_TSK_FUND_TRANSFER_REC TR "
                + "         INNER JOIN TBL_SYS_CLIENT C "
                + "         ON TR.ACCEPTCLIENTID = C.CLIENTID "
                + "         AND C.ISDELETED = '0' "
                + "         INNER JOIN TBL_DVL_PRODUCT PRODUCT "
                + "            ON PRODUCT.PRODUCTID = TR.PRODUCTID "
                + "            AND PRODUCT.ISDELETED = '0' "
                + "            AND PRODUCT.ISVALID = '1' "
                + "         INNER JOIN TBL_DVL_CONTRACT CONTRACT "
                + "            ON CONTRACT.CONTRACTID = PRODUCT.CONTRACTID "
                + "            AND CONTRACT.TRADINGPLATFORM = 'nyjtfl_3' "
                + "            AND CONTRACT.ISDELETED = '0' "
                + "            AND CONTRACT.ISVALID = '1' "
                + "         WHERE (TR.STATUS = 'transferstatus_confirmed' "
                + "            OR TR.STATUS = 'transferstatus_invalid') "
                + "            AND CONTRACT.PROJECTID <> '405937938dea46c49784e1b9381821cb' "
                + "        ) T "
                + "  WHERE "
                + "        T.LASTMODIFIEDTIME > TO_TIMESTAMP('2016-4-1 00:00:00.000000', 'YYYY-MM-DD HH24:MI:SSXFF') "


                ;

        System.out.println(sql);
    }
}
